﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_personal_offduty')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_personal_offduty

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-21 00:24:02
-- Database:    [scm_main-hysj2]
-- Description: 个人中心-员工当前调休信息
-- =============================================

CREATE PROCEDURE bd_proc_personal_offduty
  @userSerial BIGINT,
  @date DATE
AS
BEGIN
  SET NOCOUNT ON;
  

  CREATE TABLE #result
  (
    user_serial BIGINT,
    user_no VARCHAR(50),
    user_name VARCHAR(100),
    dep_id BIGINT,
    dep_name VARCHAR(100),
    off_duty_total INT DEFAULT 0,
    off_duty_usage INT DEFAULT 0,
    off_duty_remain INT DEFAULT 0,

    off_duty_total_hours DECIMAL(8,2) DEFAULT 0,
    off_duty_usage_hours DECIMAL(8,2) DEFAULT 0,
    off_duty_remain_hours DECIMAL(8,2) DEFAULT 0
  )

  INSERT INTO #result(user_serial, user_no, user_name, dep_id, dep_name)
  SELECT user_serial, user_no, user_lname, user_dep, dep_name
  FROM bd_view_user_all
  WHERE user_serial = @userSerial 

  DECLARE @settleDate DATETIME, @year INT, @month INT
  SET @settleDate = dbo.bd_fn_getSettleDate(@date)
  SELECT @year = YEAR(@settleDate), @month = MONTH(@settleDate)

  UPDATE r
  SET off_duty_total = ISNULL(ht.save_ordinary, 0) + ISNULL(ht.save_weekend, 0), off_duty_remain = ISNULL(ht.rest_total, 0)
  FROM #result r
  INNER JOIN bd_kq_holidays_time ht ON ht.user_serial = r.user_serial AND ht.time_year = @year AND ht.time_month = @month

  --已使用额度
  UPDATE #result SET off_duty_usage = off_duty_total - off_duty_remain

  UPDATE #result
  SET off_duty_total_hours = CAST(off_duty_total / 60.0 AS DECIMAL(8, 2))
     ,off_duty_usage_hours = CAST(off_duty_usage / 60.0 AS DECIMAL(8, 2))
     ,off_duty_remain_hours = CAST(off_duty_remain / 60.0 AS DECIMAL(8, 2))


  SELECT * FROM #result 


  DROP TABLE #result

  SET NOCOUNT OFF;
END